/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.freshdirect.src.dao;

import com.freshdirect.src.model.Order;
import com.freshdirect.src.model.Product;
import com.freshdirect.src.model.ShoppingCart;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import javax.sql.DataSource;

/**
 *
 * @author Ilias
 */
public class OrderDAOImpl implements OrderDAO{

    private DataSource dataSource;
    
    @Override
    public void setDataSource(String filename) {
        Properties props = new Properties();
        InputStream fis = null;
        MysqlDataSource mysqlDS = null;
        try {
            fis = getClass().getResourceAsStream("/"+filename+".properties");
            props.load(fis);
            mysqlDS = new MysqlDataSource();
            mysqlDS.setURL(props.getProperty("DB_URL"));
            mysqlDS.setUser(props.getProperty("DB_USERNAME"));
            mysqlDS.setPassword(props.getProperty("DB_PASSWORD"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        this.dataSource = mysqlDS;
    }
    
    @Override
    public int save(ShoppingCart cart, int userId) {
        int id = 0;
        int orderID = 0;
        System.out.println("1");
        
        // CREATE ORDER
        String query = "call createorder(?,?,?)";
        Connection con = null;
        CallableStatement cs = null;
        try{
            con = dataSource.getConnection();
            cs = con.prepareCall(query);
            cs.setInt(1,userId);
            cs.setDouble(2,cart.getTotalPrice());
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.execute();
            System.out.println("2");
            orderID = cs.getInt(3);
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                cs.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }      
        }
        System.out.println("3");
        // CREATE ORDER DETAIL FOR EVERY PRODUCT IN CART AND UPDATE PRODUCT QUANTITY
        for(int i=0;i<cart.getProducts().size(); i++){
            System.out.println("4");
            query = "call createorderdetail(?,?,?,?)";
            con = null;
            try{
                con = dataSource.getConnection();
                cs = con.prepareCall(query);
                cs.setInt(1, orderID);
                cs.setInt(2,cart.getProducts().get(i).getId());
                cs.setInt(3,cart.getProducts().get(i).getQuantity());
                cs.registerOutParameter(4, java.sql.Types.INTEGER);
                cs.execute();
                int out = cs.getInt(4);
                if(out !=0){
                    System.out.println("Order Detail saved");
                }
                else {
                    System.out.println("Order Detail failed!");
                    id = i;
                    break;
                }
            }catch(SQLException e){
                e.printStackTrace();
            }finally{
                try {
                    cs.close();
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }      
            }
        }
        System.out.println("5");
        // DELETE ORDER DETAIL AND FIX PRODUCT QUANTITY
        if (id != 0){
            for(int i=0; i<id; i++){
                System.out.println("Order Detail restored!");
                query = "call deleteorderdetail(?,?,?)";
                con = null;
                cs = null;
                try{
                    con = dataSource.getConnection();
                    cs = con.prepareCall(query);
                    cs.setInt(1, orderID);
                    cs.setInt(2,cart.getProducts().get(i).getId());
                    cs.setInt(3,cart.getProducts().get(i).getQuantity());
                    cs.execute();
                }catch(SQLException e){
                    e.printStackTrace();
                }finally{
                    try {
                        cs.close();
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }      
                }
            }
       
        // DELETE ORDER
        query = "call deleteorder(?)";
        con = null;
        cs = null;
        try{
            con = dataSource.getConnection();
            cs = con.prepareCall(query);
            cs.setInt(1,orderID);
            cs.execute();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                cs.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }      
        }
        }
        
        
        
        return id;
    }

    @Override
    public List<Order> getAllById(int id) {
        String query = "call getordersbyid(?)";
        List<Order> orders = new ArrayList<Order>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int orderID = 0;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            Order order = new Order();
            order.setTotalPrice(0);
            while(rs.next()){
                if( rs.getInt(1) == orderID){      
                    Product product = new Product();
                    product.setName(rs.getString(2));
                    product.setQuantity(rs.getInt(3));
                    order.getProducts().add(product);
                }
                else{
                    if(order.getTotalPrice()!= 0){
                        orders.add(order);
                    }
                    orderID = rs.getInt(1);
                    order = new Order();
                    order.setId(rs.getInt(1));
                    order.setTotalPrice(rs.getInt(4));
                    order.setDate(rs.getTimestamp(5));
                    Product product = new Product();
                    product.setName(rs.getString(2));
                    product.setQuantity(rs.getInt(3));
                    order.getProducts().add(product);
                }
            }
            if(order.getTotalPrice()!=0){
                orders.add(order);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return orders;
    }
    
}
